What is the role of Functions in SQL server ?

Question

Anonymous User · Answer

EXAMPLE:-
and --Create a table 
CREATE TABLE Employee
(
and EmpID int PRIMARY KEY,
and FirstName varchar(50) NULL,
and LastName varchar(50) NULL,
and Salary int NULL,
and Address varchar(100) NULL,
)
--Insert Data
Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(1,'Mohan','Chauahn',22000,'Delhi');
Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Asif','Khan',15000,'Delhi');
Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhuvnesh','Shakya',19000,'Noida');
Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida');
--See created table
Select * from Employeeand 
--Create function to get emp full name 
Create function fnGetEmpFullName
(
and @FirstName varchar(50),
and @LastName varchar(50)
)
returns varchar(101)
As
Begin return (Select @FirstName and#43; ' 'and#43; @LastName);
endand 
and --Calling the above created function
Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employeeand 
and --Create function to get employees
Create function fnGetEmployee()
returns Table
As
and return (Select * from Employee) 
and --Now call the above created function
Select * from fnGetEmployee()and 
--Create function for EmpID,FirstName and Salary of Employee
Create function fnGetMulEmployee()
returns @Emp Table
(
EmpID int, 
FirstName varchar(50),
Salary int
)
As
begin
and Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;
--Now update salary of first employee
and update @Emp set Salary=25000 where EmpID=1;
--It will update only in @Emp table not in Original Employee table
return
end 
and --Now call the above created function
Select * from fnGetMulEmployee() 
and --Now see the original table. This is not affected by above function update command
Select * from Employeeand 
For practice only.and

Anonymous User · Answer

A (SQL) function is a database object in SQL Server. Primarily, this is a set of SQL statements that accept only input parameters, perform actions and return the result. The function can return an only a single value or a table. We can’t use a function to Insert, Update, Delete records in the database tables. 
Unlike Stored Procedure, Function returns an only single value. and Unlike Stored Procedure, Function accepts only input parameters. and Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in a database table(s).
and Like Stored Procedure, Function can be nested up to 32 levels. and User Defined Function can have up to 1023 input parameters while a Stored Procedure can have up to 2100 input parameters.
and User Defined Function can't return XML Data Type. and User Defined Function doesn't support Exception handling. and User Defined Function can call only Extended Stored Procedure. and User Defined Function doesn't support set options like set ROWCOUNT etc.

forum

What is the role of Functions in SQL server ?

Anonymous User

Can you answer this question?

2 Answers

Liked By